/*
 * 文件名：BillRepairServiceImpl.java
 * 版权：Copyright by www.tiansu-china.com/
 * 描述：
 * 修改人：congriran
 * 修改时间：2020年11月30日
 * 跟踪单号：
 * 修改单号：
 * 修改内容：
*/

package com.tiansu.reborn.wanda.modules.executor.service.impl;

import java.io.IOException;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;
import org.springframework.web.multipart.MultipartFile;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.tiansu.reborn.wanda.modules.executor.dao.billrepair.BillRepairDao;
import com.tiansu.reborn.wanda.modules.executor.dao.billrepair.BillRepairIService;
import com.tiansu.reborn.wanda.modules.executor.entity.BillDayEntity;
import com.tiansu.reborn.wanda.modules.executor.entity.SampleEntity;
import com.tiansu.reborn.wanda.modules.executor.entity.SamplingDataEntity;
import com.tiansu.reborn.wanda.modules.executor.entity.vo.BillRepairCondVO;
import com.tiansu.reborn.wanda.modules.executor.entity.vo.BillRepairExcelVO;
import com.tiansu.reborn.wanda.modules.executor.entity.vo.SamplingDataExeclVO;
import com.tiansu.reborn.wanda.modules.executor.service.BillRepairService;
import com.tiansu.reborn.wanda.modules.executor.service.listener.BillRepairUploadListener;

import lombok.extern.slf4j.Slf4j;

@Service
@Slf4j
public class BillRepairServiceImpl implements BillRepairService
{
    @Autowired
    private BillRepairIService billRepairIService;

    @Autowired
    private BillRepairDao billRepairDao;

    @Override
    public void downloadBill(HttpServletResponse response, BillRepairCondVO vo) throws IOException
    {
        if (vo.getDeviceId() == null)
        {
            List<Integer> deviceIds = billRepairDao.queryDeviceId(vo.getDeviceNo());
            if (!CollectionUtils.isEmpty(deviceIds))
            {
                vo.setDeviceId(deviceIds.get(0));
            }
            else
            {
                log.error("DeviceNo: " + vo.getDeviceNo() + " does not exist!");
                return;
            }
        }
        SimpleDateFormat formatter = new SimpleDateFormat("yyyyMMddHHmmss");
        String now = formatter.format(new Date());
        response.setCharacterEncoding("utf-8");
        response.setContentType("application/vnd.ms-excel");
        // 这里URLEncoder.encode可以防止中文乱码
        response.setHeader("Content-disposition",
                "attachment;filename=" + URLEncoder.encode("账单修复_", "UTF-8") + now + ".xlsx");
        ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();

        List<BillRepairExcelVO> billExcelVOs = getBillExcelVOS(vo);
        WriteSheet writeSheet = EasyExcel.writerSheet(1, "账单数据").head(BillRepairExcelVO.class).build();
        excelWriter.write(billExcelVOs, writeSheet);

        List<SamplingDataExeclVO> hourSamplingDatas = getSamplingDatas(vo, "tb_samplehour_ext");
        writeSheet = EasyExcel.writerSheet(2, "时冻结数据").head(SamplingDataExeclVO.class).build();
        excelWriter.write(hourSamplingDatas, writeSheet);

        List<SamplingDataExeclVO> daySamplingDatas = getSamplingDatas(vo, "tb_sampleday_ext");
        writeSheet = EasyExcel.writerSheet(3, "日冻结数据").head(SamplingDataExeclVO.class).build();
        excelWriter.write(daySamplingDatas, writeSheet);

        // 千万别忘记finish 会帮忙关闭流
        excelWriter.finish();
    }

    /**
     * Description: 获取账单数据
     * 
     * @param vo 查询条件
     * @return 账单数据
     * @see
     */
    private List<BillRepairExcelVO> getBillExcelVOS(BillRepairCondVO vo)
    {
        List<BillDayEntity> billEntities = billRepairDao.queryBillDay(vo);
        List<BillRepairExcelVO> billExcelVOs = new ArrayList<BillRepairExcelVO>();
        for (BillDayEntity billEntity : billEntities)
        {
            BillRepairExcelVO billVO = new BillRepairExcelVO(billEntity);
            billExcelVOs.add(billVO);
        }
        return billExcelVOs;
    }

    /**
     * Description: 获取时/日冻结数据
     * 
     * @param vo        查询参数
     * @param tableName 表名
     * @return 时/日冻结数据
     * @see
     */
    private List<SamplingDataExeclVO> getSamplingDatas(BillRepairCondVO vo, String tableName)
    {
        List<SamplingDataExeclVO> hourSamplingDatas = new ArrayList<SamplingDataExeclVO>();
        List<SampleEntity> sampleEnties = billRepairDao.queryTableColumn(tableName, vo.getDeviceId());
        if (!CollectionUtils.isEmpty(sampleEnties) && sampleEnties.size() >= 5)
        {
            StringBuffer sql = new StringBuffer("select ");
            sql.append(sampleEnties.get(0).getSampletablename()).append(".timeid as timeid, ");
            sql.append(sampleEnties.get(0).getSampletablename()).append(".").append(sampleEnties.get(0).getColumnname())
                    .append(" as sum, ");
            sql.append(sampleEnties.get(1).getSampletablename()).append(".").append(sampleEnties.get(1).getColumnname())
                    .append(" as tip, ");
            sql.append(sampleEnties.get(2).getSampletablename()).append(".").append(sampleEnties.get(2).getColumnname())
                    .append(" as peak, ");
            sql.append(sampleEnties.get(3).getSampletablename()).append(".").append(sampleEnties.get(3).getColumnname())
                    .append(" as valley, ");
            sql.append(sampleEnties.get(4).getSampletablename()).append(".").append(sampleEnties.get(4).getColumnname())
                    .append(" as flat");
            sql.append(" from ").append(sampleEnties.get(0).getSampletablename());
            int i = getNextTableName(sampleEnties);
            if (0 != i)
            {
                sql.append(" left join ").append(sampleEnties.get(i).getSampletablename());
                sql.append(" on ").append(sampleEnties.get(0).getSampletablename()).append(".timeid = ")
                        .append(sampleEnties.get(i).getSampletablename()).append(".timeid");
            }
            sql.append(" where ").append(sampleEnties.get(0).getSampletablename()).append(".timeid >='")
                    .append(vo.getStartDate());
            sql.append("' and ").append(sampleEnties.get(0).getSampletablename()).append(".timeid <='")
                    .append(vo.getEndDate()).append("';");
            log.debug("QuerySamplingData sql is :" + sql);
            List<SamplingDataEntity> samplingDataEntities = billRepairDao.querySamplingData(sql);
            for (SamplingDataEntity entity : samplingDataEntities)
            {
                SamplingDataExeclVO excelVO = new SamplingDataExeclVO(entity);
                hourSamplingDatas.add(excelVO);
            }
        }
        return hourSamplingDatas;
    }

    /**
     * Description: 查找第一个不同的表名
     * 
     * @param sampleEnties
     * @return 下标
     * @see
     */
    private int getNextTableName(List<SampleEntity> sampleEnties)
    {
        String tableName = sampleEnties.get(0).getSampletablename();
        for (int i = 1; i < sampleEnties.size(); i++)
        {
            String secTableName = sampleEnties.get(i).getSampletablename();
            if (!tableName.equalsIgnoreCase(secTableName))
            {
                return i;
            }
        }
        return 0;
    }

    @Override
    public void uploadBill(MultipartFile file) throws IOException
    {
        EasyExcel.read(file.getInputStream(), BillRepairExcelVO.class, new BillRepairUploadListener(billRepairIService))
                .sheet().doRead();
    }
}
